from urllib.parse import urlencode
import requests
original_url='https://www.autohome.com.cn/ashx/AjaxIndexHotCarByDsj.ashx?cityid=110100'
requests_headers={
    'Referer':'https://www.autohome.com.cn/beijing/',
    'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36',
    'X-Requested-With':'XMLHttpRequest'
}
import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute("DROP DATABASE IF EXISTS AJAX")
cursor.execute("CREATE DATABASE AJAX DEFAULT CHARACTER SET utf8mb4")
db.close()
db2 = pymysql.connect(host='localhost', user='root', password='123456', port=3306, database='AJAX')
cursor2 = db2.cursor()
cursor2.execute("DROP TABLE IF EXISTS ajax")
sql1 = """CREATE TABLE `ajax`(
            `car_name` char(20) NOT NULL,
            `id` int(10) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (`id`)
            )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
cursor2.execute(sql1)
print("Greated table Successfull.")
def get_one(cityid):
    p={
        'cityid':cityid
    }
    complete_url=original_url+urlencode(p)
    try:
        response=requests.get(url=complete_url,params=requests_headers)
        if response.status_code==200:
            return response.json()
    except requests.ConnectionError as e:
        print('Error',e.args)
def parse_three(json):
    if json:
        for i in json:
            for b in i.get('SeriesList'):
                item_list=b.get('Name')
                item_list2=b.get('Id')
                print(item_list+':'+str(item_list2))
                sql2='INSERT INTO ajax(car_name,id) VALUES(%s,%s)'
                try:
                    cursor2.execute(sql2,(item_list,item_list2))
                    db2.commit()
                except:
                    db2.rollback()
if __name__=='__main__':
    city_list=[{'北京':'110100'},{'重庆':'500100'},{'上海':'310100'}]
for city in city_list:
    jo=get_one(city.values())
    parse_three(jo)
db2.close()


